package com.chenjiagou.bookingdemo.mapper;

import com.chenjiagou.bookingdemo.entity.Booking;
import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Optional;

@Mapper
public interface BookingMapper {

    /**
     * 插入订单
     */
    @Insert("INSERT INTO booking (booking_no, user_id, flight_id, seat_class_id, status, total_amount, deposit_paid, expire_time, created_at) " +
            "VALUES (#{bookingNo}, #{userId}, #{flightId}, #{seatClassId}, #{status}, #{totalAmount}, #{depositPaid}, #{expireTime}, #{createdAt})")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int insertBooking(Booking booking);

    /**
     * 查询所有订单，带用户名
     */
    @Select("SELECT b.*, u.username FROM booking b LEFT JOIN user u ON b.user_id = u.id")
    @Results({
        @Result(property = "id", column = "id"),
        @Result(property = "booking_no", column = "booking_no"),
        @Result(property = "user_id", column = "user_id"),
        @Result(property = "flight_id", column = "flight_id"),
        @Result(property = "seat_class_id", column = "seat_class_id"),
        @Result(property = "status", column = "status"),
        @Result(property = "total_amount", column = "total_amount"),
        @Result(property = "deposit_paid", column = "deposit_paid"),
        @Result(property = "expire_time", column = "expire_time"),
        @Result(property = "created_at", column = "created_at"),
        @Result(property = "username", column = "username")
    })
    List<com.chenjiagou.bookingdemo.entity.BookingWithUsername> getAllBookingsWithUsername();

    /**
     * 根据ID查询订单
     */
    @Select("SELECT * FROM booking WHERE id = #{id}")
    Optional<Booking> getBookingById(Long id);

    /**
     * 更新订单信息
     */
    @Update("UPDATE booking SET " +
            "booking_no = #{bookingNo}, " +
            "user_id = #{userId}, " +
            "flight_id = #{flightId}, " +
            "seat_class_id = #{seatClassId}, " +
            "status = #{status}, " +
            "total_amount = #{totalAmount}, " +
            "deposit_paid = #{depositPaid}, " +
            "expire_time = #{expireTime}, " +
            "created_at = #{createdAt} " +
            "WHERE id = #{id}")
    int updateBooking(Booking booking);

    /**
     * 取消订单
     */
    @Update("UPDATE booking SET status = 4 WHERE id = #{id}")
    int cancelBooking(Long id);

    /**
     * 根据ID删除订单
     */
    @Delete("DELETE FROM booking WHERE id = #{id}")
    int deleteBookingById(Long id);

    /**
     * 通过用户名查找订单（多表关联）
     */
    @Select("SELECT b.*, u.username FROM booking b JOIN user u ON b.user_id = u.id WHERE u.username = #{username}")
    @Results({
        @Result(property = "id", column = "id"),
        @Result(property = "booking_no", column = "booking_no"),
        @Result(property = "user_id", column = "user_id"),
        @Result(property = "flight_id", column = "flight_id"),
        @Result(property = "seat_class_id", column = "seat_class_id"),
        @Result(property = "status", column = "status"),
        @Result(property = "total_amount", column = "total_amount"),
        @Result(property = "deposit_paid", column = "deposit_paid"),
        @Result(property = "expire_time", column = "expire_time"),
        @Result(property = "created_at", column = "created_at"),
        @Result(property = "username", column = "username")
    })
    List<com.chenjiagou.bookingdemo.entity.BookingWithUsername> findBookingsByUsername(String username);

    /**
     * 根据ID查询订单详情，带座位号、航班号、航空公司、机场信息、时间信息和乘客姓名
     */
    @Select( "SELECT " +
            "b.*, " +
            "(SELECT u.username FROM user u WHERE u.id = b.user_id) AS username, " +
            "(SELECT bs.seat_no FROM booking_seat bs WHERE bs.booking_id = b.id LIMIT 1) AS seat_no, " +
            "(SELECT f.flight_no FROM flight f WHERE f.id = b.flight_id) AS flight_no, " +
            "(SELECT f.airline FROM flight f WHERE f.id = b.flight_id) AS airline, " +
            "(SELECT f.dep_airport FROM flight f WHERE f.id = b.flight_id) AS dep_airport, " +
            "(SELECT f.arr_airport FROM flight f WHERE f.id = b.flight_id) AS arr_airport, " +
            "(SELECT f.dep_date FROM flight f WHERE f.id = b.flight_id) AS dep_date, " +
            "(SELECT f.dep_time FROM flight f WHERE f.id = b.flight_id) AS dep_time, " +
            "(SELECT f.arr_date FROM flight f WHERE f.id = b.flight_id) AS arr_date, " +
            "(SELECT f.arr_time FROM flight f WHERE f.id = b.flight_id) AS arr_time, " +
            "(SELECT p.full_name FROM passenger p WHERE p.user_id = b.user_id LIMIT 1) AS full_name " +
            "FROM booking b " +
            "WHERE b.id =#{id}")
    @Results({
        @Result(property = "id", column = "id"),
        @Result(property = "booking_no", column = "booking_no"),
        @Result(property = "user_id", column = "user_id"),
        @Result(property = "flight_id", column = "flight_id"),
        @Result(property = "seat_class_id", column = "seat_class_id"),
        @Result(property = "status", column = "status"),
        @Result(property = "total_amount", column = "total_amount"),
        @Result(property = "deposit_paid", column = "deposit_paid"),
        @Result(property = "expire_time", column = "expire_time"),
        @Result(property = "created_at", column = "created_at"),
        @Result(property = "username", column = "username"),
        @Result(property = "seatNo", column = "seat_no"),
        @Result(property = "flightNo", column = "flight_no"),
        @Result(property = "airline", column = "airline"),
        @Result(property = "depAirport", column = "dep_airport"),
        @Result(property = "arrAirport", column = "arr_airport"),
        @Result(property = "depDate", column = "dep_date"),
        @Result(property = "depTime", column = "dep_time"),
        @Result(property = "arrDate", column = "arr_date"),
        @Result(property = "arrTime", column = "arr_time"),
        @Result(property = "passengerName", column = "full_name")
    })
    Optional<com.chenjiagou.bookingdemo.entity.BookingWithUsername> getBookingDetailById(Long id);

    /**
     * 按状态查询订单
     */
    @Select("SELECT b.*, u.username FROM booking b LEFT JOIN user u ON b.user_id = u.id WHERE b.status = #{status}")
    @Results({
        @Result(property = "id", column = "id"),
        @Result(property = "booking_no", column = "booking_no"),
        @Result(property = "user_id", column = "user_id"),
        @Result(property = "flight_id", column = "flight_id"),
        @Result(property = "seat_class_id", column = "seat_class_id"),
        @Result(property = "status", column = "status"),
        @Result(property = "total_amount", column = "total_amount"),
        @Result(property = "deposit_paid", column = "deposit_paid"),
        @Result(property = "expire_time", column = "expire_time"),
        @Result(property = "created_at", column = "created_at"),
        @Result(property = "username", column = "username")
    })
    List<com.chenjiagou.bookingdemo.entity.BookingWithUsername> getBookingsByStatus(byte status);

    /**
     * 查询非指定状态的订单
     */
    @Select("SELECT b.*, u.username FROM booking b LEFT JOIN user u ON b.user_id = u.id WHERE b.status != #{status}")
    @Results({
        @Result(property = "id", column = "id"),
        @Result(property = "booking_no", column = "booking_no"),
        @Result(property = "user_id", column = "user_id"),
        @Result(property = "flight_id", column = "flight_id"),
        @Result(property = "seat_class_id", column = "seat_class_id"),
        @Result(property = "status", column = "status"),
        @Result(property = "total_amount", column = "total_amount"),
        @Result(property = "deposit_paid", column = "deposit_paid"),
        @Result(property = "expire_time", column = "expire_time"),
        @Result(property = "created_at", column = "created_at"),
        @Result(property = "username", column = "username")
    })
    List<com.chenjiagou.bookingdemo.entity.BookingWithUsername> getBookingsByStatusNot(byte status);
}